"""
Case Type   : 兼容函数
Case Name   : 验证conv()在查询语句中的交互使用
Create At   : 2023.09.04
Owner       : zhanghuan96
Description :
    1、创建兼容b库库并查询插件
    2、建表向表中插入数据包含conv()函数
    3、建表向表中插入数据包含conv()函数
    4、验证conv()作为join的on条件
    5、验证conv()作为join的on条件
    6、验证conv()结合聚合函数
    7、清理环境
Expect      :
    1、成功
    2、成功
    3、成功
    4、成功
    5、成功
    6、成功
    7、成功
History     :modified by zhanghuan96，2023.09.04，yat执行结果的精度处理和手工执行
             结果不一致， 修改用例为py确保执行结果同手工执行结果一致
            modified by qumin20145，2024.03.04，类似select 1!这种单后置操作符语法无法支持，同步优化用例
"""

import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class CompatibilityTest09(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.common = Common()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = 'db_conv_0009'
        self.tb_name = 'tb_conv_0009'
        self.common = Common()

    def test_create_function(self):
        text = '-----step1:创建兼容b库库并查询插件;expect:成功-----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
            f"create database {self.db_name} dbcompatibility ='B';"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS,
                      sql_res, '兼容B库创建失败')

        sql_cmd1 = "select extname from pg_extension where extname ='dolphin';"
        self.log.info(sql_cmd1)
        sql_res1 = self.sh_primary.execut_db_sql(sql_cmd1, dbname=self.db_name)
        self.log.info(sql_res1)
        self.assertIn('dolphin', sql_res1, '查询失败')

        text = '-----step2:建表向表中插入数据包含conv()函数;expect:成功-----'
        self.log.info(text)
        sql_cmd2 = f'''drop table if exists {self.tb_name};
        create table {self.tb_name}
        (id int,num float8,name varchar(20) default '张三',score float8);
        insert into {self.tb_name}(id,num,score) values
        (1,conv(100101,2,10),conv(10101,2,10)^2),(2,conv('2c',14,9),\
        conv('2c',-14,9)^3),(3,conv(5,10,5),conv(5,10,2)^2),\
        (4,-conv('28d',14,8),conv('2d',14,8)),(5,conv('28d',14,8),\
        conv('28c',14,8)^2),(6,conv('21',15,8)%5,conv('21',15,8)^2),\
        (7,conv('c',14,7)-2,conv('b',14,7)^2),(8,conv('c',15,7)*9,\
        conv('c',14,9)^2),(9,conv(5,10,2),conv(5,10,3)^(-2)),\
        (10,conv('21',15,8)^(0.5),conv('21',15,8)^2);'''
        self.log.info(sql_cmd2)
        sql_res2 = self.sh_primary.execut_db_sql(sql_cmd2, dbname=self.db_name)
        self.log.info(sql_res2)
        self.assertIn('CREATE TABLE', sql_res2, '执行失败' + text)
        self.assertIn('INSERT 0 10', sql_res2, '执行失败' + text)

        text = '-----step3:建表向表中插入数据包含conv()函数;expect:成功-----'
        self.log.info(text)
        sql_cmd3 = f'''drop table if exists {self.tb_name}_01;
        create table {self.tb_name}_01
        (id int,num float8,name varchar(20) default '李四',score float8);\
        insert into {self.tb_name}_01(id,num,score) values
        (1,conv(1101,2,10),conv(10001,2,10)^2),(2,conv('5c',15,9),\
        conv('2a',-13,9)^3),(3,conv(5,10,5),conv(5,10,2)^2),\
        (4,-conv('28d',27,8),conv('2d',14,8)),(5,conv('28d',14,6),\
        conv('28c',17,8)^2),(6,conv('21',15,8)%5,conv('21',15,8)^2),\
        (7,conv('c',14,7)-2,conv('b',14,7)^2),(8,conv('c',15,7)*9,\
        conv('c',14,9)^2),(9,conv(5,10,2),conv(5,10,3)^(-2)),\
        (10,conv('21',13,8)^(0.5),conv('11',15,8)^2);'''
        self.log.info(sql_cmd3)
        sql_res3 = self.sh_primary.execut_db_sql(sql_cmd3, dbname=self.db_name)
        self.log.info(sql_res3)
        self.assertIn('CREATE TABLE', sql_res3, '执行失败' + text)
        self.assertIn('INSERT 0 10', sql_res3, '执行失败' + text)

        text = '-----step4:验证conv()结合各查询关键字;expect:成功-----'
        self.log.info(text)
        sql_cmd4 = f"select * from {self.tb_name} where conv(1,2,10)>0 " \
            f"order by id;"
        self.log.info(sql_cmd4)
        sql_res4 = self.sh_primary.execut_db_sql(sql_cmd4, dbname=self.db_name)
        self.log.info(sql_res4)
        rows, _ = self.common.parse_sql_query_res(sql_res4)
        expect = [['1', '37', '张三', '23'],
                  ['2', '44', '张三', '47'],
                  ['3', '10', '张三', '103'],
                  ['4', '-1005', '张三', '51'],
                  ['5', '1005', '张三', '1006'],
                  ['6', '2', '张三', '39'],
                  ['7', '13', '张三', '12'],
                  ['8', '135', '张三', '15'],
                  ['9', '101', '张三', '-14'],
                  ['10', '6.08276253029822', '张三', '39']]
        self.assertEqual(rows, expect, '执行失败' + text)

        sql_cmd5 = f"select num from {self.tb_name} group by num order by num;"
        self.log.info(sql_cmd5)
        sql_res5 = self.sh_primary.execut_db_sql(sql_cmd5, dbname=self.db_name)
        self.log.info(sql_res5)
        rows, _ = self.common.parse_sql_query_res(sql_res5)
        expect = [['-1005'], ['2'], ['6.08276253029822'], ['10'], ['13'],
                  ['37'], ['44'], ['101'], ['135'], ['1005']]
        self.assertEqual(rows, expect, '执行失败' + text)

        sql_cmd6 = f"select num from {self.tb_name} group by num having " \
            f"-conv(5,10,10)<0 order by num;"
        self.log.info(sql_cmd6)
        sql_res6 = self.sh_primary.execut_db_sql(sql_cmd6, dbname=self.db_name)
        self.log.info(sql_res6)
        rows, _ = self.common.parse_sql_query_res(sql_res6)
        expect = [['-1005'], ['2'], ['6.08276253029822'], ['10'], ['13'],
                  ['37'], ['44'], ['101'], ['135'], ['1005']]
        self.assertEqual(rows, expect, '执行失败' + text)

        sql_cmd7 = f"select * from {self.tb_name} order by id desc;"
        self.log.info(sql_cmd7)
        sql_res7 = self.sh_primary.execut_db_sql(sql_cmd7, dbname=self.db_name)
        self.log.info(sql_res7)
        rows, _ = self.common.parse_sql_query_res(sql_res7)
        expect = [['10', '6.08276253029822', '张三', '39'],
                  ['9', '101', '张三', '-14'],
                  ['8', '135', '张三', '15'],
                  ['7', '13', '张三', '12'],
                  ['6', '2', '张三', '39'],
                  ['5', '1005', '张三', '1006'],
                  ['4', '-1005', '张三', '51'],
                  ['3', '10', '张三', '103'],
                  ['2', '44', '张三', '47'],
                  ['1', '37', '张三', '23']]
        self.assertEqual(rows, expect, '执行失败' + text)

        sql_cmd8 = f"select * from {self.tb_name} order by score desc,num " \
            f"asc limit conv(11,2,10);"
        self.log.info(sql_cmd8)
        sql_res8 = self.sh_primary.execut_db_sql(sql_cmd8, dbname=self.db_name)
        self.log.info(sql_res8)
        rows, _ = self.common.parse_sql_query_res(sql_res8)
        expect = [['5', '1005', '张三', '1006'],
                  ['3', '10', '张三', '103'],
                  ['4', '-1005', '张三', '51']]
        self.assertEqual(rows, expect, '执行失败' + text)

        text = '-----step5:验证conv()作为join的on条件;expect: 查询成功-----'
        self.log.info(text)
        sql_cmd9 = f"select distinct t2.id from {self.tb_name} t1 join " \
            f"{self.tb_name}_01 t2 on t1.score = " \
            f"conv('21',15,8)^2 order by t2.id;"
        self.log.info(sql_cmd9)
        sql_res9 = self.sh_primary.execut_db_sql(sql_cmd9, dbname=self.db_name)
        self.log.info(sql_res9)
        rows, _ = self.common.parse_sql_query_res(sql_res9)
        expect = [['1'], ['2'], ['3'], ['4'], ['5'], ['6'], ['7'], ['8'],
                  ['9'], ['10']]
        self.assertEqual(rows, expect, '执行失败' + text)

        text = '-----step6:验证conv()结合聚合函数;expect: 查询成功-----'
        self.log.info(text)
        sql_cmd10 = f"select avg(num),sum(score),count(num),min(num)," \
            f"max(score) from {self.tb_name};"
        self.log.info(sql_cmd10)
        sql_res10 = self.sh_primary.execut_db_sql(sql_cmd10,
                                                  dbname=self.db_name)
        self.log.info(sql_res10)
        rows, _ = self.common.parse_sql_query_res(sql_res10)
        expect = [
            ['34.8082762530298', '1321', '10', '-1005', '1006']]
        self.assertEqual(rows, expect, '执行失败' + text)

    def tearDown(self):
        text = '-----step7:清理环境;expect:成功-----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS,
                      sql_res, '清理环境失败')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
